package net.adetech.port.service.impl;

import net.adetech.port.entity.PCGTicket;
import net.adetech.port.service.ISyncService;
import org.springframework.stereotype.Service;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;


@Service
public class SyncServiceImpl implements ISyncService{





    public static String sfg = "长岛";

    public static int maxId = 0;

    public static String database = "";

    private static Map<String,String> zdMap = new HashMap<String,String>();
    static {
        zdMap.put("01","蓬莱");
        zdMap.put("02","砣矶");
        zdMap.put("03","大钦");
        zdMap.put("04","小钦");
        zdMap.put("05","南隍");
        zdMap.put("06","北隍");
        zdMap.put("07","大黑山");
        zdMap.put("08","小黑山");
        zdMap.put("09","庙岛");
    }
    @Override
    public List<PCGTicket> getPcgTicketList() {

//        final String sql = "SELECT k.*,h.hb_time KCSJ,(SELECT name from SP_SHIP s where k.SHIPID=s.CODE) HBMC FROM (SELECT * from SP_KPJL where SP_TIME > dateadd(HOUR,-1,getdate())) k  LEFT JOIN (SELECT * from SP_HBJH where CONVERT(VARCHAR(32),hb_rq,111) = ? ) h on k.hbbh = h.hb_bh order by k.id desc";
//        String date = new SimpleDateFormat("yyyy/MM/dd").format(new Date());
//        List<PCGTicket> pcgTickets = jdbcTemplate.query(sql,new Object[]{date},new PcgTicketRowMapper());
//        return pcgTickets;
//
        List<PCGTicket> pcgTickets = new ArrayList<PCGTicket>();
        Connection con = null;
        try
        {
            Class.forName( "net.sourceforge.jtds.jdbc.Driver");
            con = DriverManager.getConnection( "jdbc:jtds:sqlserver://"+database+"/spxt_new;integratedSecurity=false", "sa", "");    //10.0.2.2是使用模拟器是用的本机（pc）的IP，androidSample是我的数据库名称

            final String sql = "SELECT top 500 k.*,(select h.hb_time from  SP_HBJH h where h.hb_rq = k.hbrq and k.hbbh = h.hb_bh) KCSJ,(SELECT name from SP_SHIP s where k.SHIPID=s.CODE) HBMC,(SELECT x.FL_NAME from SP_XLPFL x where x.ZD_ID = k.ZDID) ZDMC FROM (SELECT * from SP_KPJL where ID > "+maxId+" and SP_TIME > dateadd(HOUR,-3,getdate())) k  order by k.id asc";
            Statement stmt = con.createStatement();//创建Statement
            ResultSet rs = stmt.executeQuery(sql);//ResultSet类似Cursor
            while (rs.next())
            {
                PCGTicket ticket = new PCGTicket();
                if (rs.getString("ID")!=null)
                {
                    ticket.setId(rs.getString("ID"));
                    int id = Integer.parseInt(rs.getString("ID"));
                    if (id>maxId)
                    {
                        maxId = id;
                    }
                }
                if (rs.getString("SFZXM")!=null)
                {
                    ticket.setXm(rs.getString("SFZXM").trim());
                }
                if (rs.getString("NOTE1")!=null)
                {
                    ticket.setSfzh(rs.getString("NOTE1").trim());
                }
                if (ticket.getSfzh()!=null&&ticket.getSfzh().length()==18)
                {
                    ticket.setZjlx("身份证");
                }
                if (rs.getTimestamp("SP_TIME")!=null)
                {
                    ticket.setSpsj(rs.getTimestamp("SP_TIME"));

                }
                if (rs.getString("KCSJ")!=null)
                {

                    Timestamp kcsj = getFullDateFromTimeString(rs.getString("KCSJ"),ticket.getSpsj());
                    ticket.setKcsj(kcsj);
                }
                if (rs.getString("HBMC")!=null)
                {

                    ticket.setBc(rs.getString("HBMC"));

                }
                if (rs.getString("XH")!=null)
                {
                    ticket.setCw(rs.getString("XH"));
                }
                ticket.setSfg(sfg);
                if (rs.getString("ZDMC")!=null)
                {
                    ticket.setDdg(rs.getString("ZDMC").trim().replace("零担",""));
                }





                ticket.setRksj(new Timestamp(new Date().getTime()));
                ticket.setGxsj(new Timestamp(new Date().getTime()));
                pcgTickets.add(ticket);

            }
            rs.close();
            stmt.close();
        }catch (Exception e)
        {
            e.printStackTrace();
        }
        finally {
            if (con!=null)
            {
                try {
                    con.close();
                } catch (SQLException e) {
                }
            }
        }
        return pcgTickets;




    }

    private static Timestamp getFullDateFromTimeString(String timeString,Timestamp date)
    {
        Timestamp timestamp = null;
        try {
            timeString = timeString.substring(11,19);
            String dateString = new SimpleDateFormat("yyyy-MM-dd").format(new Date(date.getTime()));
            timestamp = new Timestamp(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(dateString+" "+timeString).getTime());
        }
        catch (Exception e)
        {

        }
        return timestamp;
    }

}